﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Collections;

namespace Data.Database
{
    public class Personal
    {
        static private String cadenaConexion = ConfigurationManager.ConnectionStrings["Turnet"].ConnectionString; // @"Data Source=|DataDirectory|\BBDD.sdf";
        static private String nombreTabla = "Personal";
       
        public void AnyadirPersonal(DataSet personal)
        {
            DataSet dsPersonal = new DataSet();
            try
            {
                SqlConnection conexion = new SqlConnection(cadenaConexion);
                String consulta = "Select * from Personal";
                SqlDataAdapter daPersonal = new SqlDataAdapter(consulta, conexion);
                daPersonal.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                daPersonal.Fill(dsPersonal, nombreTabla); //nombre de tabla esta declarado arriba del todo y sera "Personal"
                SqlCommandBuilder cbuilder = new SqlCommandBuilder(daPersonal);
                daPersonal.Update(personal, "Personal");

            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
            public DataSet ObtenerTablaPersonalcrear()
        {
            DataSet dsPersonal = new DataSet();

            try
            {
                SqlConnection conexion = new SqlConnection(cadenaConexion);
                String consulta = "SELECT * FROM Personal";
                SqlDataAdapter daPersonal = new SqlDataAdapter(consulta, conexion);
                daPersonal.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                daPersonal.Fill(dsPersonal, nombreTabla);
            }
            catch (Exception ex)
            {
                throw (ex);
            }

            return dsPersonal;
            }


            public DataSet ObtenerTablaPersonal(string todo)
            {
                DataSet dsPersonal = new DataSet();

                try
                {
                    SqlConnection conexion = new SqlConnection(cadenaConexion);
                    //String consulta = "Select * FROM Personal WHERE ciudad like '%"+ciu+"%' and apellidos='"+apell+"'";

                    if (todo != "") //si cadena todo esta llena
                    {
                        String consulta = "Select * FROM Personal WHERE " + todo + "";
                        SqlDataAdapter daPersonal = new SqlDataAdapter(consulta, conexion);
                        daPersonal.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                        daPersonal.Fill(dsPersonal, nombreTabla); //dsPersonal es ahora nuestra base de datos local
                    }
                    else //si cadena todo esta vacia
                    {
                        String consulta = "Select * FROM Personal";
                        SqlDataAdapter daPersonal = new SqlDataAdapter(consulta, conexion);
                        daPersonal.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                        daPersonal.Fill(dsPersonal, nombreTabla); //dsPersonal es ahora nuestra base de datos local
                    }



                }
                catch (Exception ex)
                {
                    throw (ex);
                }

                return dsPersonal;
            }

            public void EditarPersonal(DataSet personal)
        {
            DataSet dsPersonal = new DataSet();
            try
            {
                
                SqlConnection conexion = new SqlConnection(cadenaConexion);

                String consulta = "Select * from Personal where DNI ='" + personal.Tables["Personal"].Rows[0][1] +"'";
                SqlDataAdapter daPersonal = new SqlDataAdapter(consulta, conexion);
                daPersonal.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                daPersonal.Fill(dsPersonal, nombreTabla);
                SqlCommandBuilder cbuilder = new SqlCommandBuilder(daPersonal);
                daPersonal.Update(personal, nombreTabla);

            }
            catch (Exception ex)
            {
                Exception excepcionManejada = new Exception("Error al modificar datos de usuario", ex);
                throw ex;
            }
                
                
            
            
         }
        //DESARROLLAR
        public DataSet ObtenerDatosPersonalConDni(string dni)
        {
            DataSet dsPersonal = new DataSet();

            try
            {
                SqlConnection conexion = new SqlConnection(cadenaConexion);
                String consulta = "SELECT * FROM Personal WHERE DNI='"  + dni + "'";
                SqlDataAdapter daPersonal = new SqlDataAdapter(consulta, conexion);
                daPersonal.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                daPersonal.Fill(dsPersonal, nombreTabla);
                
            }
            catch (Exception ex)
            {
                Exception excepcionManejada = new Exception("Error al modificar datos de usuario", ex);
                
                throw (ex);
            }

            return dsPersonal;
        }


        public DataSet ConseguirTipoPersonal()
        {
            DataSet dsTipoPersonal = new DataSet();

            try
            {
                SqlConnection conexion = new SqlConnection(cadenaConexion);
                String consulta = "Select *  FROM TipoPersonal";
                SqlDataAdapter daTipoPersonal = new SqlDataAdapter(consulta, conexion);
                daTipoPersonal.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                daTipoPersonal.Fill(dsTipoPersonal, "TipoPersonal"); //dsPersonal es ahora nuestra base de datos local
            }
            catch (Exception ex)
            {
                throw (ex);
            }

            return dsTipoPersonal;
        }






        public void BorrarPersonal(string dni)
        {
            DataSet dsPersonal = new DataSet();
            try
            {

                SqlConnection conexion = new SqlConnection(cadenaConexion);
                String consulta = "SELECT * FROM Personal WHERE DNI='" + dni + "'";
                SqlDataAdapter daPersonal = new SqlDataAdapter(consulta, conexion);
                daPersonal.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                daPersonal.Fill(dsPersonal, nombreTabla);

                dsPersonal.Tables["Personal"].Rows[0].Delete();

                SqlCommandBuilder cbuilder = new SqlCommandBuilder(daPersonal);
                daPersonal.Update(dsPersonal, "Personal");

            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
    }
}
